package com.kaizeli.website.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.kaizeli.website.pojo.dto.EmployeeActiveExcelDTO;
import com.kaizeli.website.service.EmployeeActiveImportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * 在职员工导入服务实现类
 *
 * @author Kaizeli
 * @date 2024-10-21
 */
@Slf4j
@Service
public class EmployeeActiveImportServiceImpl implements EmployeeActiveImportService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> importEmployeeActiveExcel(MultipartFile file, boolean updateIfExists) {
        Map<String, Object> result = new HashMap<>();

        // 校验文件
        if (file == null || file.isEmpty()) {
            result.put("success", false);
            result.put("message", "文件为空，请上传Excel文件");
            return result;
        }

        String filename = file.getOriginalFilename();
        if (filename == null || (!filename.endsWith(".xlsx") && !filename.endsWith(".xls"))) {
            result.put("success", false);
            result.put("message", "文件格式错误，仅支持Excel文件（.xlsx或.xls）");
            return result;
        }

        // 用于统计
        final int[] successCount = {0};
        final int[] updateCount = {0};
        final int[] failCount = {0};
        final List<String> errorMessages = new ArrayList<>();
        final Set<String> existingIdCards = new HashSet<>();

        log.info("开始导入在职员工Excel，更新模式：{}", updateIfExists ? "存在则更新" : "存在则跳过");

        try (InputStream inputStream = file.getInputStream()) {
            EasyExcel.read(inputStream, EmployeeActiveExcelDTO.class, new ReadListener<EmployeeActiveExcelDTO>() {

                @Override
                public void invoke(EmployeeActiveExcelDTO data, AnalysisContext context) {
                    Integer rowNum = context.readRowHolder().getRowIndex() + 1;

                    try {
                        // 数据校验
                        String validationError = validateData(data, rowNum);
                        if (validationError != null) {
                            errorMessages.add(validationError);
                            failCount[0]++;
                            return;
                        }

                        // 检查身份证号是否在Excel中重复
                        if (existingIdCards.contains(data.getIdCard())) {
                            errorMessages.add(String.format("第%d行：身份证号 [%s] 在Excel中重复", rowNum, data.getIdCard()));
                            failCount[0]++;
                            return;
                        }

                        // 检查数据库中是否已存在该身份证号
                        String checkSql = "SELECT COUNT(*) FROM employee_active WHERE id_card = ?";
                        Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, data.getIdCard());
                        boolean exists = (count != null && count > 0);

                        if (exists) {
                            if (updateIfExists) {
                                // 更新模式
                                String updateSql = "UPDATE employee_active SET " +
                                        "archive_no = ?, project_code = ?, client_company = ?, department = ?, team = ?, " +
                                        "position = ?, employee_type = ?, name = ?, gender = ?, marital_status = ?, " +
                                        "native_place = ?, nationality = ?, political_status = ?, birthday = ?, " +
                                        "registered_address = ?, current_address = ?, phone = ?, email = ?, " +
                                        "hire_date = ?, regular_date = ?, education_degree = ?, major = ?, " +
                                        "graduate_school = ?, graduate_date = ?, skill_certificates = ?, base_salary = ?, " +
                                        "emergency_contact_name = ?, emergency_contact_phone = ?, bank_card_no = ?, bank_name = ?, " +
                                        "is_contract_renewed = ?, contract_renewal_count = ?, latest_contract_start_date = ?, " +
                                        "latest_contract_end_date = ?, latest_renewal_contract_period = ?, latest_contract_company = ?, " +
                                        "contract_history_period = ?, contract_history_company = ?, remarks = ? " +
                                        "WHERE id_card = ?";

                                jdbcTemplate.update(updateSql,
                                        data.getArchiveNo(), data.getProjectCode(), data.getClientCompany(), data.getDepartment(), data.getTeam(),
                                        data.getPosition(), data.getEmployeeType(), data.getName(), data.getGender(), data.getMaritalStatus(),
                                        data.getNativePlace(), data.getNationality(), data.getPoliticalStatus(), data.getBirthday(),
                                        data.getRegisteredAddress(), data.getCurrentAddress(), data.getPhone(), data.getEmail(),
                                        data.getHireDate(), data.getRegularDate(), data.getEducationDegree(), data.getMajor(),
                                        data.getGraduateSchool(), data.getGraduateDate(), data.getSkillCertificates(), data.getBaseSalary(),
                                        data.getEmergencyContactName(), data.getEmergencyContactPhone(), data.getBankCardNo(), data.getBankName(),
                                        data.getIsContractRenewed(), data.getContractRenewalCount(), data.getLatestContractStartDate(),
                                        data.getLatestContractEndDate(), data.getLatestRenewalContractPeriod(), data.getLatestContractCompany(),
                                        data.getContractHistoryPeriod(), data.getContractHistoryCompany(), data.getRemarks(),
                                        data.getIdCard()
                                );

                                updateCount[0]++;
                                log.info("成功更新在职员工：{} - {}", data.getIdCard(), data.getName());
                            } else {
                                // 跳过模式
                                errorMessages.add(String.format("第%d行：身份证号 [%s] 已存在于数据库中（跳过）",
                                        rowNum, data.getIdCard()));
                                failCount[0]++;
                                return;
                            }
                        } else {
                            // 插入新数据
                            String insertSql = "INSERT INTO employee_active (archive_no, project_code, client_company, department, team, " +
                                    "position, employee_type, name, gender, marital_status, native_place, nationality, political_status, " +
                                    "id_card, birthday, registered_address, current_address, phone, email, hire_date, regular_date, " +
                                    "education_degree, major, graduate_school, graduate_date, skill_certificates, base_salary, " +
                                    "emergency_contact_name, emergency_contact_phone, bank_card_no, bank_name, is_contract_renewed, " +
                                    "contract_renewal_count, latest_contract_start_date, latest_contract_end_date, " +
                                    "latest_renewal_contract_period, latest_contract_company, contract_history_period, " +
                                    "contract_history_company, remarks) " +
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                            jdbcTemplate.update(insertSql,
                                    data.getArchiveNo(), data.getProjectCode(), data.getClientCompany(), data.getDepartment(), data.getTeam(),
                                    data.getPosition(), data.getEmployeeType(), data.getName(), data.getGender(), data.getMaritalStatus(),
                                    data.getNativePlace(), data.getNationality(), data.getPoliticalStatus(), data.getIdCard(), data.getBirthday(),
                                    data.getRegisteredAddress(), data.getCurrentAddress(), data.getPhone(), data.getEmail(),
                                    data.getHireDate(), data.getRegularDate(), data.getEducationDegree(), data.getMajor(),
                                    data.getGraduateSchool(), data.getGraduateDate(), data.getSkillCertificates(), data.getBaseSalary(),
                                    data.getEmergencyContactName(), data.getEmergencyContactPhone(), data.getBankCardNo(), data.getBankName(),
                                    data.getIsContractRenewed(), data.getContractRenewalCount(), data.getLatestContractStartDate(),
                                    data.getLatestContractEndDate(), data.getLatestRenewalContractPeriod(), data.getLatestContractCompany(),
                                    data.getContractHistoryPeriod(), data.getContractHistoryCompany(), data.getRemarks()
                            );

                            successCount[0]++;
                            log.info("成功新增在职员工：{} - {}", data.getIdCard(), data.getName());
                        }

                        existingIdCards.add(data.getIdCard());

                    } catch (Exception e) {
                        log.error("导入第{}行数据失败：{}", rowNum, e.getMessage(), e);
                        errorMessages.add(String.format("第%d行：导入失败 - %s", rowNum, e.getMessage()));
                        failCount[0]++;
                    }
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    log.info("在职员工Excel读取完成，新增：{}条，更新：{}条，失败：{}条",
                            successCount[0], updateCount[0], failCount[0]);
                }
            }).sheet().doRead();

            // 返回结果
            int totalSuccess = successCount[0] + updateCount[0];
            String message = updateIfExists ?
                    String.format("导入完成！新增：%d条，更新：%d条，失败：%d条", successCount[0], updateCount[0], failCount[0]) :
                    String.format("导入完成！成功：%d条，失败：%d条", successCount[0], failCount[0]);

            result.put("success", true);
            result.put("message", message);
            result.put("successCount", successCount[0]);
            result.put("updateCount", updateCount[0]);
            result.put("totalSuccess", totalSuccess);
            result.put("failCount", failCount[0]);
            result.put("errors", errorMessages);

        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            result.put("success", false);
            result.put("message", "读取Excel文件失败：" + e.getMessage());
        } catch (Exception e) {
            log.error("导入Excel文件失败", e);
            result.put("success", false);
            result.put("message", "导入失败：" + e.getMessage());
        }

        return result;
    }

    private String validateData(EmployeeActiveExcelDTO data, Integer rowNum) {
        // 姓名（必填）
        if (data.getName() == null || data.getName().trim().isEmpty()) {
            return String.format("第%d行：姓名不能为空", rowNum);
        }

        // 身份证号（必填且唯一）
        if (data.getIdCard() == null || data.getIdCard().trim().isEmpty()) {
            return String.format("第%d行：身份证号不能为空", rowNum);
        }
        if (!data.getIdCard().matches("^\\d{17}[0-9Xx]$")) {
            return String.format("第%d行：身份证号格式不正确", rowNum);
        }

        return null;
    }

    @Override
    public byte[] downloadTemplate() {
        ByteArrayOutputStream outputStream = null;
        try {
            outputStream = new ByteArrayOutputStream();

            List<EmployeeActiveExcelDTO> templateData = new ArrayList<>();

            // 示例数据
            EmployeeActiveExcelDTO example = new EmployeeActiveExcelDTO();
            example.setArchiveNo("A001");
            example.setProjectCode("PROJ-2024-001");
            example.setClientCompany("示例公司");
            example.setDepartment("技术部");
            example.setTeam("开发组");
            example.setPosition("Java工程师");
            example.setEmployeeType("正式员工");
            example.setName("张三");
            example.setGender("男");
            example.setMaritalStatus("未婚");
            example.setNativePlace("北京");
            example.setNationality("汉族");
            example.setPoliticalStatus("群众");
            example.setIdCard("110101199001011234");
            example.setBirthday(new Date());
            example.setRegisteredAddress("北京市朝阳区XX街道");
            example.setCurrentAddress("北京市海淀区XX小区");
            example.setPhone("13800138000");
            example.setEmail("zhangsan@example.com");
            example.setHireDate(new Date());
            example.setEducationDegree("本科");
            example.setMajor("计算机科学与技术");
            example.setGraduateSchool("XX大学");
            example.setBaseSalary(new BigDecimal("10000.00"));
            example.setEmergencyContactName("李四");
            example.setEmergencyContactPhone("13900139000");
            example.setBankCardNo("6222001234567890123");
            example.setBankName("中国工商银行XX支行");
            example.setIsContractRenewed(0);
            example.setContractRenewalCount(0);
            example.setLatestContractStartDate(new Date());
            example.setRemarks("示例数据");
            templateData.add(example);

            EasyExcel.write(outputStream, EmployeeActiveExcelDTO.class)
                    .sheet("在职员工导入模板")
                    .doWrite(templateData);

            return outputStream.toByteArray();

        } catch (Exception e) {
            log.error("生成模板文件失败", e);
            throw new RuntimeException("生成模板文件失败：" + e.getMessage(), e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("关闭输出流失败", e);
                }
            }
        }
    }
}

